package com.example.javaairportflightanalysis.dao;

import com.example.javaairportflightanalysis.domin.information.EventFlight;
import com.example.javaairportflightanalysis.domin.information.FlightNumberGroupDtime;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface InformationMapper {
    @Select("SELECT dtime, COUNT(*) AS count\n" +
            "FROM zbtj.dfdl_A\n" +
            "GROUP BY dtime;")
    List<FlightNumberGroupDtime> selectFlightNumberA2();

    @Select("SELECT dtime, COUNT(*) AS count\n" +
            "FROM zbtj.TRansfer_A\n" +
            "GROUP BY dtime;")
    List<FlightNumberGroupDtime> selectFlightNumberA3();

    @Select("SELECT dtime, COUNT(*) AS count\n" +
            "FROM zbtj.dfdl_D\n" +
            "GROUP BY dtime;")
    List<FlightNumberGroupDtime> selectFlightNumberD2();

    @Select("SELECT dtime, COUNT(*) AS count\n" +
            "FROM zbtj.Transfer_D\n" +
            "GROUP BY dtime;")
    List<FlightNumberGroupDtime> selectFlightNumberD3();


    @Select("select DISTINCT DA.dtime,\n" +
            "COUNT(DDD.IARS) as IARS_count,\n" +
            "COUNT(DCD.ABST) as ABST_count,\n" +
            "COUNT(*) as total_count\n" +
            "from zbtj.DFDL_A DA\n" +
            "left JOIN zbtj.\"DFME_DLYE_DFLT\" DDD ON DA.DFLT_FLID=DDD.FLID \n" +
            "left join zbtj.\"DFME_CANE_DFLT\" DCD ON DCD.FLID=DA.DFLT_FLID\n" +
            "GROUP BY DA.dtime")
    List<EventFlight> selectEventFlightA2();
    @Select("select DISTINCT DA.dtime,\n" +
            "COUNT(DDD.IARS) as IARS_count,\n" +
            "COUNT(DCD.ABST) as ABST_count,\n" +
            "COUNT(*) as total_count\n" +
            "from zbtj.DFDL_D DA\n" +
            "left JOIN zbtj.\"DFME_DLYE_DFLT\" DDD ON DA.DFLT_FLID=DDD.FLID \n" +
            "left join zbtj.\"DFME_CANE_DFLT\" DCD ON DCD.FLID=DA.DFLT_FLID\n" +
            "GROUP BY DA.dtime")
    List<EventFlight> selectEventFlightD2();
    @Select("select DISTINCT DA.dtime,\n" +
            "COUNT(DDD.IARS) as IARS_count,\n" +
            "COUNT(DCD.ABST) as ABST_count,\n" +
            "COUNT(*) as total_count\n" +
            "from zbtj.TRANSFER_A DA\n" +
            "left JOIN zbtj.\"DFME_DLYE_DFLT\" DDD ON DA.DFLT_FLID=DDD.FLID \n" +
            "left join zbtj.\"DFME_CANE_DFLT\" DCD ON DCD.FLID=DA.DFLT_FLID\n" +
            "GROUP BY DA.dtime")
    List<EventFlight> selectEventFlightA3();
    @Select("select DISTINCT DA.dtime,\n" +
            "COUNT(DDD.IARS) as IARS_count,\n" +
            "COUNT(DCD.ABST) as ABST_count,\n" +
            "COUNT(*) as total_count\n" +
            "from zbtj.TRANSFER_D DA\n" +
            "left JOIN zbtj.\"DFME_DLYE_DFLT\" DDD ON DA.DFLT_FLID=DDD.FLID \n" +
            "left join zbtj.\"DFME_CANE_DFLT\" DCD ON DCD.FLID=DA.DFLT_FLID\n" +
            "GROUP BY DA.dtime")
    List<EventFlight> selectEventFlightD3();

}
